import os
import sys
import pandas as pd
from window import Form
from string import ascii_uppercase
from PyQt5.QtWidgets import QApplication, QWidget, QFileDialog
from openpyxl.styles import Alignment, Border, Side, PatternFill, Font


# 主窗口
class MainSystem(QWidget, Form):
    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.signal_groove()
        self.file = None

    # 信号与槽，给按钮添加事件（即点击后要调用的函数）
    def signal_groove(self):
        pass
        # self.pushButton_1.clicked.connect(self.open_file)
        # self.pushButton_2.clicked.connect(self.create_file)

    # 打开文件
    def open_file(self):
        open_file_name = QFileDialog.getOpenFileNames(self, '选择文件')
        if open_file_name[0]:
            self.lineEdit_11.setText(open_file_name[0][0])
            self.label_12.setText('')

    # 生成文件
    def create_file(self):
        self.file = self.lineEdit_11.text()  # 文件
        if not self.file:
            self.label_12.setText('请选择目标文件!')
            self.label_12.setStyleSheet("color: rgb(255,0,0)")
            self.lineEdit_2.clear()
            return

        if not os.path.exists(self.file):
            self.label_12.setText('目标文件不存在!')
            self.label_12.setStyleSheet("color: rgb(255,0,0)")
            self.lineEdit_2.clear()
            return

        self.label_12.setText('')

        self.data_deal()

    # 数据处理
    def data_deal(self):
        file_name = '数据分析结果.xlsx'
        file_path = os.path.dirname(self.file)
        save_file = os.path.join(file_path, file_name)

        data_list = []
        try:
            df = pd.read_excel(self.file, header=1)
            by_list = ['倍数', '百分比', '持续时间', 'A/B', '杠杆倍数']
            group_by = df.groupby(by_list)
            each_tuple: tuple
            for each_tuple, each_df in group_by:
                each_dict = dict(zip(by_list, list(each_tuple)))
                each_dict.update({'最大盈利数据': '最大盈利数据'})
                each_dict.update({'最小手续费': each_df['合计手续费'].min()})
                each_dict.update({'最大手续费': each_df['合计手续费'].max()})
                each_dict.update({'最小盈利': each_df['合计净盈亏'].min()})
                each_dict.update({'最大盈利': each_df['合计净盈亏'].max()})
                each_dict.update({'平均手续费': each_df['合计手续费'].mean()})
                each_dict.update({'平均盈利金额': each_df['合计净盈亏'].mean()})
                max_index = each_df['合计净盈亏'].idxmax()
                max_series = each_df.loc[max_index]
                each_dict.update({'手续费': max_series['合计手续费']})
                each_dict.update({'止盈百分比': max_series['止盈百分比']})
                each_dict.update({'盈利金额': max_series['合计净盈亏']})

                data_list.append(each_dict)
        except Exception as e:
            print('目标文件格式有误！', e)
            self.label_12.setText('目标文件格式有误!')
            self.label_12.setStyleSheet("color: rgb(255,0,0)")
            self.lineEdit_2.clear()
            return

        try:
            data_df = pd.DataFrame(data_list)
            group_by = data_df.groupby('杠杆倍数')
            columns_list = ['倍数', '百分比', '持续时间/秒', '最小手续费', '最大手续费', '最小盈利', '最大盈利',
                            '最大盈利数据', '手续费', '止盈百分比', '盈利金额', '平均手续费', '平均盈利金额']

            ascii_list = [i for i in ascii_uppercase]
            font = Font(size=25, color='FF0000')
            pattern_fill = PatternFill(start_color='F5F7F9', end_color='F5F7F9', fill_type='solid')
            alignment_ = Alignment(horizontal='center', vertical='center', wrap_text=True)
            border_ = Border(left=Side(border_style='thin', color='FF000000'),
                             right=Side(border_style='thin', color='FF000000'),
                             top=Side(border_style='thin', color='FF000000'),
                             bottom=Side(border_style='thin', color='FF000000'))

            excel_writer = pd.ExcelWriter(save_file, engine='openpyxl')
            for each_sheet, each_df in group_by:
                each_df['持续时间/秒'] = each_df['持续时间']
                each_df = each_df[columns_list]

                key_sheet = f'{each_sheet}倍'
                each_df.to_excel(excel_writer, key_sheet, index=False)

                sheet = excel_writer.sheets[key_sheet]

                sheet.insert_rows(1)  # 插入行
                sheet.cell(row=1, column=1, value=f'{each_sheet}倍数据总结').font = font

                rows = sheet.max_row
                ascii_dict = dict(zip(each_df.columns, ascii_list))
                sheet.merge_cells(f"{list(ascii_dict.values())[0]}1:{list(ascii_dict.values())[-1]}1")  # 合并单元格

                # 冻结行
                sheet.freeze_panes = 'A2'
                sheet.freeze_panes = 'A3'

                for key, value in ascii_dict.items():
                    sheet.row_dimensions[1].height = 35
                    sheet.row_dimensions[2].height = 25
                    sheet[value + str(2)].fill = pattern_fill
                    if key in ['倍数', '百分比', '持续时间/秒', '止盈百分比']:
                        sheet.column_dimensions[value].width = 12
                    else:
                        sheet.column_dimensions[value].width = 16
                    for index in range(rows):
                        sheet[value + str(index + 1)].alignment = alignment_
                        sheet[value + str(index + 1)].border = border_

            excel_writer.close()
            self.label_12.setText('文件已生成!')
            self.label_12.setStyleSheet("color: rgb(0,255,0)")
            self.lineEdit_2.setText(save_file)
        except Exception as e:
            print(f'文件：{file_name} 保存失败！', e)
            self.label_12.setText('生成文件保存失败!')
            self.label_12.setStyleSheet("color: rgb(255,0,0)")
            self.lineEdit_2.clear()


if __name__ == "__main__":
    app = QApplication(sys.argv)
    main = MainSystem()
    main.show()
    sys.exit(app.exec_())
